﻿/***************************************************************************
	Copyright (C) 2010 RapidWebDev Organization (Author: Eunge, Legal Name: Jian Liu, Email: eunge.liu@gmail.com)

	This program is free software: you can redistribute it and/or modify
	it under the terms of the GNU General Public License as published by
	the Free Software Foundation, either version 3 of the License, or
	(at your option) any later version.

	This program is distributed in the hope that it will be useful,
	but WITHOUT ANY WARRANTY; without even the implied warranty of
	MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
	GNU General Public License for more details.

	You should have received a copy of the GNU General Public License
	along with this program.  If not, see <http://www.gnu.org/licenses/>.
 ***************************************************************************/

using System;
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Configuration;
using System.Linq;
using System.Reflection;
using System.Text.RegularExpressions;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using Spring.Objects;
using BaoJianSoft.Web.Extensions.Controls;
using BaoJianSoft.Common;
using BaoJianSoft.Web.Extensions.Properties;

namespace BaoJianSoft.Web.Extensions.DynamicPages
{
    /// <summary>
    /// Abstract base class to parse loaded excel document.
    /// </summary>
    public abstract class ExcelParser : IExcelParser
    {
        protected internal const string SUCCESS = "Success";
        protected internal const string FAILURE = "Failure";
        protected internal const string ROW_INDEX = "RowIndex";
        protected internal const string EXCEPTION = "Exception";

        private static readonly Type bindingAttributeType = typeof(BindingAttribute);
        private static readonly Regex regexToMatchText = new Regex(@"^\d{1}[\.]\d+E\+\d+$", RegexOptions.Compiled);
        private static readonly string[] trueValues = new string[] { "true", "yes", "y", "1", "是" };
        private static readonly string[] falseValues = new string[] { "false", "no", "n", "0", "否" };

        /// <summary>
        /// Set/get ExcelDocument on how to parse excel document.
        /// </summary>
        public ExcelDocument Document { get; set; }

        /// <summary>
        /// On attaching child controls. 
        /// The method attempts to load control from skin template by field name for the fields declared with BindingAttribute
        /// </summary>
        /// <param name="detailpanel"></param>
        public virtual void OnAttachChildControls(ExcelDetailPanelControl detailpanel)
        {
            Type thisType = this.GetType();
            FieldInfo[] fields = thisType.GetFields(BindingFlags.NonPublic | BindingFlags.Instance);
            foreach (FieldInfo field in fields)
            {
                object[] bindings = field.GetCustomAttributes(bindingAttributeType, true);
                if (bindings == null || bindings.Length == 0) continue;

                // Last attribute overrides all before
                BindingAttribute binding = bindings[bindings.Length - 1] as BindingAttribute;
                if (Kit.IsEmpty(binding.ParentControlPath))
                {
                    field.SetValue(this, detailpanel.FindControl(field.Name));
                }
                else
                {
                    string[] parentControlIds = binding.ParentControlPath.Split(new string[] { @"\", "/" }, StringSplitOptions.RemoveEmptyEntries);
                    Control parentControl = detailpanel;
                    foreach (string parentControlId in parentControlIds)
                    {
                        if (parentControl == null) throw new InvalidProgramException(string.Format(Resources.ParentControlNotFoundInSkinTemplate, binding.ParentControlPath, detailpanel.SkinPath));
                        parentControl = parentControl.FindControl(parentControlId);
                    }

                    field.SetValue(this, parentControl.FindControl(field.Name));
                }
            }
        }

        /// <summary>
        /// Fires on validating excel cell value parsing to property value.
        /// </summary>
        /// <param name="e"></param>
        public virtual void OnValidatingPropertyValue(ExcelValidatingFieldEventArgs e) { e.Exception = null; e.IsValid = true; }

        /// <summary>
        /// Fires on parsing excel cell value to property value.
        /// </summary>
        /// <param name="e"></param>
        public virtual void OnParsingPropertyValue(ExcelParsingPropertyEventArgs e) { e.PropertyValue = e.CellValue; }

        /// <summary>
        /// Fires on saving objects parsed from uploaded excel document.
        /// </summary>
        /// <param name="objectsUploaded"></param>
        public virtual void OnSaving(ExcelParserObjectEventArgs e) { }

        /// <summary>
        /// Fires after saved objects parsed from uploaded excel document.
        /// </summary>
        /// <param name="e"></param>
        public virtual void OnSaved(ExcelParserObjectEventArgs e) { }

        /// <summary>
        /// Returns dataset composes of two datatable, Success and Failure.
        /// </summary>
        /// <param name="excelFilePath"></param>
        /// <returns></returns>
        public DataSet Parse(string excelFilePath)
        {
            return this.LoadExcel(excelFilePath);
        }

        /// <summary>
        /// Parse uploaded dataset to instance typed in DocumentObjectType.
        /// </summary>
        /// <param name="dataSet"></param>
        /// <returns></returns>
        public IList Parse(DataSet dataSet)
        {
            if (!dataSet.Tables.Contains(ExcelParser.SUCCESS))
                throw new InvalidProgramException(Resources.ArgumentDatasetNotParsedFromUploadExcel);

            ArrayList results = new ArrayList();
            IObjectWrapper wrapper = new ObjectWrapper(this.Document.EntityType);

            foreach (DataRow excelRow in dataSet.Tables[ExcelParser.SUCCESS].Rows)
            {
                object instance = Activator.CreateInstance(this.Document.EntityType);
                wrapper.WrappedInstance = instance;
                results.Add(instance);

                foreach (ExcelField excelField in this.Document.Fields)
                {
                    object cellValue = excelRow[excelField.FieldName];
                    object propertyValue = null;
                    if (cellValue != null && cellValue != DBNull.Value)
                    {
                        propertyValue = ConvertFieldValue(excelField, cellValue);
                        if (excelField.CallbackToParseValue)
                        {
                            ExcelParsingPropertyEventArgs e = new ExcelParsingPropertyEventArgs(excelField.PropertyName, excelField.FieldName, propertyValue);
                            this.OnParsingPropertyValue(e);
                            propertyValue = e.PropertyValue;
                        }
                        wrapper.SetPropertyValue(excelField.PropertyName, propertyValue);
                    }
                }
            }

            return results;
        }

        /// <summary>
        /// Load datatable from excel document and convert to the schema matches ExcelDocument configuration.
        /// </summary>
        /// <param name="path">local excel document path.</param>
        /// <returns>Load datatable instance from excel document.</returns>
        private DataSet LoadExcel(string excelFilePath)
        {
            if (this.Document == null)
            {
                throw new InvalidProgramException(Resources.PropertyDocumentCannotBeNull);
            }

            DataTable loadedDataTable = this.LoadExternalExcelDocument(excelFilePath);
            DataSet returnedDataSet = this.CreateEmptyByExcelDocument();

            Hashtable primaryKeys = new Hashtable();
            for (int index = 0; index < loadedDataTable.Rows.Count; index++)
            {
                string exception;
                if (!this.ValidateExcelDataRow(loadedDataTable.Rows[index], out exception))
                {
                    DataRow exceptionRow = returnedDataSet.Tables[ExcelParser.FAILURE].NewRow();
                    exceptionRow[ExcelParser.ROW_INDEX] = index + 1;
                    exceptionRow[ExcelParser.EXCEPTION] = exception;
                    returnedDataSet.Tables[ExcelParser.FAILURE].Rows.Add(exceptionRow);
                }
                else
                {
                    // check primary field value is duplicate
                    object primaryKey = loadedDataTable.Rows[index][this.Document.PrimaryKeyField.FieldName];
                    if (primaryKeys.ContainsKey(primaryKey))
                    {
                        DataRow exceptionRow = returnedDataSet.Tables[ExcelParser.FAILURE].NewRow();
                        exceptionRow[ExcelParser.ROW_INDEX] = index + 1;
                        exceptionRow[ExcelParser.EXCEPTION] = string.Format(Resources.DuplicateInExcelDocument, this.Document.PrimaryKeyField.FieldName, primaryKey);
                        returnedDataSet.Tables[ExcelParser.FAILURE].Rows.Add(exceptionRow);
                    }
                    else
                    {
                        primaryKeys.Add(primaryKey, null);
                        DataRow documentRow = returnedDataSet.Tables[ExcelParser.SUCCESS].NewRow();
                        this.ParseToDocumentRow(loadedDataTable.Rows[index], documentRow);
                        returnedDataSet.Tables[ExcelParser.SUCCESS].Rows.Add(documentRow);
                    }
                }
            }

            returnedDataSet.AcceptChanges();
            return returnedDataSet;
        }

        #region Excel Data Row Validation

        /// <summary>
        /// Validate excel data row.
        /// </summary>
        /// <param name="row"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        private bool ValidateExcelDataRow(DataRow row, out string exception)
        {
            exception = null;
            foreach (ExcelField excelField in this.Document.Fields)
            {
                if (!row.Table.Columns.Contains(excelField.FieldName)) continue;
                else
                {
                    object cellValue = row[excelField.FieldName];
                    if (cellValue == null || cellValue == DBNull.Value)
                    {
                        if (!excelField.IsNullable)
                        {
                            exception = string.Format(Resources.FieldValueCannotBeEmpty, excelField.FieldName);
                            return false;
                        }
                        else
                        {
                            continue;
                        }
                    }
                    else
                    {
                        if (!this.ValidateExcelFieldValue(excelField, cellValue, out exception)) return false;
                    }
                }
            }

            return true;
        }

        /// <summary>
        /// Validate excel field value.
        /// </summary>
        /// <param name="excelField"></param>
        /// <param name="cellValue"></param>
        /// <returns></returns>
        private bool ValidateExcelFieldValue(ExcelField excelField, object cellValue, out string exception)
        {
            if (cellValue == null) throw new ArgumentNullException("cellValue");

            switch (excelField.FieldType)
            {
                case ExcelFieldTypes.Text:
                    return this.ValidateExcelFieldStringValue(excelField, cellValue.ToString(), out exception);
                case ExcelFieldTypes.Decimal:
                    return this.ValidateExcelFieldDecimalValue(excelField, cellValue.ToString(), out exception);
                case ExcelFieldTypes.Double:
                    return this.ValidateExcelFieldDoubleValue(excelField, cellValue.ToString(), out exception);
                case ExcelFieldTypes.Int32:
                    return this.ValidateExcelFieldInt32Value(excelField, cellValue.ToString(), out exception);
                case ExcelFieldTypes.DateTime:
                    return this.ValidateExcelFieldDateTimeValue(excelField, cellValue.ToString(), out exception);
                case ExcelFieldTypes.Boolean:
                    return this.ValidateExcelFieldBooleanValue(excelField, cellValue.ToString(), out exception);
            }

            throw new NotSupportedException(string.Format(Resources.NotSupportExcelType, excelField.FieldType));
        }

        /// <summary>
        /// Validate string typed excel field value.
        /// </summary>
        /// <param name="excelField"></param>
        /// <param name="cellValue"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        private bool ValidateExcelFieldStringValue(ExcelField excelField, string cellValue, out string exception)
        {
            exception = null;

            if (regexToMatchText.IsMatch(cellValue))
            {
				exception = string.Format(Resources.FieldValueNotMatchFormat, excelField.FieldName, cellValue, regexToMatchText);
                return false;
            }

            if (excelField.Expression != null && !excelField.Expression.IsMatch(cellValue))
            {
                exception = string.Format(Resources.FieldValueNotMatchFormat + " [{2}]！", excelField.FieldName, cellValue, excelField.Expression.ToString());
                return false;
            }

            if (excelField.MaxLength > 0 && cellValue.Length > excelField.MaxLength)
            {
                exception = string.Format(Resources.FieldValueGreaterThanMaxLength, excelField.FieldName, cellValue, excelField.MaxLength);
                return false;
            }

            if (excelField.MinLength > 0 && cellValue.Length < excelField.MinLength)
            {
                exception = string.Format(Resources.FieldValueLessThanMinLength, excelField.FieldName, cellValue, excelField.MinLength);
                return false;
            }

            if (excelField.CallbackToValidate)
            {
                ExcelValidatingFieldEventArgs e = new ExcelValidatingFieldEventArgs(excelField.PropertyName, excelField.FieldName, cellValue);
                this.OnValidatingPropertyValue(e);
                if (!e.IsValid)
                {
                    exception = e.Exception;
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// Validate int-typed excel field value.
        /// </summary>
        /// <param name="excelField"></param>
        /// <param name="cellValue"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        private bool ValidateExcelFieldInt32Value(ExcelField excelField, string cellValue, out string exception)
        {
            exception = null;

            int fieldValue;
            if (!int.TryParse(cellValue, out fieldValue))
            {
                exception = string.Format(Resources.InvalidIntegerFieldValue, excelField.FieldName, cellValue);
                return false;
            }

            if (!string.IsNullOrEmpty(excelField.MaxValue))
            {
                int maxValue;
                if (!int.TryParse(excelField.MaxValue, out maxValue))
                {
                    throw new InvalidProgramException(string.Format(Resources.PropertyMaxValueCannotBeConvertToInt, excelField.FieldName));
                }

                if (fieldValue > maxValue)
                {
                    exception = string.Format(Resources.FieldValueGreaterThanMaxValue, excelField.FieldName, fieldValue, maxValue);
                    return false;
                }
            }

            if (!string.IsNullOrEmpty(excelField.MinValue))
            {
                int minValue;
                if (!int.TryParse(excelField.MinValue, out minValue))
                {
                    throw new InvalidProgramException(string.Format(Resources.PropertyMinValueCannotBeConvertToInt, excelField.FieldName));
                }

                if (fieldValue < minValue)
                {
                    exception = string.Format(Resources.FieldValueLessThanMinValue, excelField.FieldName, fieldValue, minValue);
                    return false;
                }
            }

            if (excelField.CallbackToValidate)
            {
                ExcelValidatingFieldEventArgs e = new ExcelValidatingFieldEventArgs(excelField.PropertyName, excelField.FieldName, cellValue);
                this.OnValidatingPropertyValue(e);
                if (!e.IsValid)
                {
                    exception = e.Exception;
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// Validate decimal-typed excel field value.
        /// </summary>
        /// <param name="excelField"></param>
        /// <param name="cellValue"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        private bool ValidateExcelFieldDecimalValue(ExcelField excelField, string cellValue, out string exception)
        {
            exception = null;

            decimal fieldValue;
            if (!decimal.TryParse(cellValue, out fieldValue))
            {
                exception = string.Format(Resources.InvalidFloatFieldValue, excelField.FieldName, cellValue);
                return false;
            }

            if (!string.IsNullOrEmpty(excelField.MaxValue))
            {
                decimal maxValue;
                if (!decimal.TryParse(excelField.MaxValue, out maxValue))
                {
                    throw new InvalidProgramException(string.Format(Resources.PropertyMaxValueCannotBeConvertToDecimal, excelField.FieldName));
                }

                if (fieldValue > maxValue)
                {
                    exception = string.Format(Resources.FieldValueGreaterThanMaxValue, excelField.FieldName, fieldValue, maxValue);
                    return false;
                }
            }

            if (!string.IsNullOrEmpty(excelField.MinValue))
            {
                decimal minValue;
                if (!decimal.TryParse(excelField.MinValue, out minValue))
                {
                    throw new InvalidProgramException(string.Format(Resources.PropertyMinValueCannotBeConvertToDecimal, excelField.FieldName));
                }

                if (fieldValue < minValue)
                {
                    exception = string.Format(Resources.FieldValueLessThanMinValue, excelField.FieldName, fieldValue, minValue);
                    return false;
                }
            }

            if (excelField.CallbackToValidate)
            {
                ExcelValidatingFieldEventArgs e = new ExcelValidatingFieldEventArgs(excelField.PropertyName, excelField.FieldName, cellValue);
                this.OnValidatingPropertyValue(e);
                if (!e.IsValid)
                {
                    exception = e.Exception;
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// Validate double-typed excel field value.
        /// </summary>
        /// <param name="excelField"></param>
        /// <param name="cellValue"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        private bool ValidateExcelFieldDoubleValue(ExcelField excelField, string cellValue, out string exception)
        {
            exception = null;

            double fieldValue;
            if (!double.TryParse(cellValue, out fieldValue))
            {
                exception = string.Format(Resources.InvalidFloatFieldValue, excelField.FieldName, cellValue);
                return false;
            }

            if (!string.IsNullOrEmpty(excelField.MaxValue))
            {
                double maxValue;
                if (!double.TryParse(excelField.MaxValue, out maxValue))
                {
                    throw new InvalidProgramException(string.Format(Resources.PropertyMaxValueCannotBeConvertToDecimal, excelField.FieldName));
                }

                if (fieldValue > maxValue)
                {
                    exception = string.Format(Resources.FieldValueGreaterThanMaxValue, excelField.FieldName, fieldValue, maxValue);
                    return false;
                }
            }

            if (!string.IsNullOrEmpty(excelField.MinValue))
            {
                double minValue;
                if (!double.TryParse(excelField.MinValue, out minValue))
                {
                    throw new InvalidProgramException(string.Format(Resources.PropertyMinValueCannotBeConvertToDecimal, excelField.FieldName));
                }

                if (fieldValue < minValue)
                {
                    exception = string.Format(Resources.FieldValueLessThanMinValue, excelField.FieldName, fieldValue, minValue);
                    return false;
                }
            }

            if (excelField.CallbackToValidate)
            {
                ExcelValidatingFieldEventArgs e = new ExcelValidatingFieldEventArgs(excelField.PropertyName, excelField.FieldName, cellValue);
                this.OnValidatingPropertyValue(e);
                if (!e.IsValid)
                {
                    exception = e.Exception;
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// Validate DateTime-typed excel field value.
        /// </summary>
        /// <param name="excelField"></param>
        /// <param name="cellValue"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        private bool ValidateExcelFieldDateTimeValue(ExcelField excelField, string cellValue, out string exception)
        {
            exception = null;

            DateTime fieldValue;
            if (!DateTime.TryParse(cellValue, out fieldValue))
            {
                exception = string.Format(Resources.InvalidDateTimeValue, excelField.FieldName, cellValue);
                return false;
            }

            if (!string.IsNullOrEmpty(excelField.MaxValue))
            {
                DateTime maxValue;
                if (!DateTime.TryParse(excelField.MaxValue, out maxValue))
                {
                    throw new InvalidProgramException(string.Format(Resources.PropertyMaxValueCannotBeConvertToDateTime, excelField.FieldName));
                }

                if (fieldValue > maxValue)
                {
                    exception = string.Format(Resources.FieldValueGreaterThanMaxValue, excelField.FieldName, fieldValue, maxValue);
                    return false;
                }
            }

            if (!string.IsNullOrEmpty(excelField.MinValue))
            {
                DateTime minValue;
                if (!DateTime.TryParse(excelField.MinValue, out minValue))
                {
                    throw new InvalidProgramException(string.Format(Resources.PropertyMinValueCannotBeConvertToDateTime, excelField.FieldName));
                }

                if (fieldValue < minValue)
                {
                    exception = string.Format(Resources.FieldValueLessThanMinValue, excelField.FieldName, fieldValue, minValue);
                    return false;
                }
            }

            if (excelField.CallbackToValidate)
            {
                ExcelValidatingFieldEventArgs e = new ExcelValidatingFieldEventArgs(excelField.PropertyName, excelField.FieldName, cellValue);
                this.OnValidatingPropertyValue(e);
                if (!e.IsValid)
                {
                    exception = e.Exception;
                    return false;
                }
            }

            return true;
        }

        /// <summary>
        /// Validate boolean-typed excel field value.
        /// </summary>
        /// <param name="excelField"></param>
        /// <param name="cellValue"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        private bool ValidateExcelFieldBooleanValue(ExcelField excelField, string cellValue, out string exception)
        {
            exception = null;

            string lowerCaseCellValue = cellValue.ToLowerInvariant();
            if (!trueValues.Contains(lowerCaseCellValue) && !falseValues.Contains(lowerCaseCellValue))
            {
                exception = string.Format(Resources.InvalidBooleanValue, excelField.FieldName, cellValue);
                return false;
            }

            if (excelField.CallbackToValidate)
            {
                ExcelValidatingFieldEventArgs e = new ExcelValidatingFieldEventArgs(excelField.PropertyName, excelField.FieldName, cellValue);
                this.OnValidatingPropertyValue(e);
                if (!e.IsValid)
                {
                    exception = e.Exception;
                    return false;
                }
            }

            return true;
        }

        #endregion

        /// <summary>
        /// Parse excel row to document row by ExcelDocument configuration.
        /// </summary>
        /// <param name="excelRow"></param>
        /// <param name="documentRow"></param>
        private void ParseToDocumentRow(DataRow excelRow, DataRow documentRow)
        {
            foreach (ExcelField excelField in this.Document.Fields)
            {
                if (!excelRow.Table.Columns.Contains(excelField.FieldName))
                {
                    documentRow[excelField.FieldName] = DBNull.Value;
                }
                else
                {
                    object cellValue = excelRow[excelField.FieldName];
                    if (cellValue == null || cellValue == DBNull.Value)
                    {
                        documentRow[excelField.FieldName] = cellValue;
                    }
                    else
                    {
                        documentRow[excelField.FieldName] = ConvertFieldValue(excelField, cellValue);
                    }
                }
            }
        }

        /// <summary>
        /// Create empty DataSet contains both successful and failed converted data.
        /// </summary>
        /// <returns></returns>
        private DataSet CreateEmptyByExcelDocument()
        {
            DataSet dataSet = new DataSet(this.Document.WorksheetName);
            DataTable success = new DataTable(ExcelParser.SUCCESS);
            foreach (ExcelField excelField in this.Document.Fields)
            {
                success.Columns.Add(excelField.FieldName, MapExcelFieldType(excelField.FieldType));
            }

            dataSet.Tables.Add(success);

            DataTable failure = new DataTable(ExcelParser.FAILURE);
            failure.Columns.Add(ExcelParser.ROW_INDEX, typeof(int));
            failure.Columns.Add(ExcelParser.EXCEPTION, typeof(string));
            dataSet.Tables.Add(failure);

            dataSet.AcceptChanges();
            return dataSet;
        }

        /// <summary>
        /// Load external excel document file and convert to data table instance.
        /// The method has ensured the excel document has included the non-nullable fields by configuration.
        /// </summary>
        /// <param name="externalExcelFilePath"></param>
        /// <returns></returns>
        private DataTable LoadExternalExcelDocument(string externalExcelFilePath)
        {
            if (!File.Exists(externalExcelFilePath))
            {
                throw new FileNotFoundException(Resources.ExcelFileNotFound, externalExcelFilePath);
            }

            string connectionString = string.Format("Provider=Microsoft.Jet.OleDb.4.0;data source={0};Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'", externalExcelFilePath);
            using (OleDbConnection conn = new OleDbConnection(connectionString))
            {
                conn.Open();
                DataTable schema = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                if (schema.Rows.Count == 0)
                {
                    throw new InvalidOperationException(Resources.ExcelWorksheetNotFound);
                }

                bool found = false;
                string worksheetName = string.Format("{0}$", this.Document.WorksheetName);
                foreach (DataRow schemaRow in schema.Rows)
                {
                    if (schemaRow[2].ToString().Trim() == worksheetName)
                    {
                        found = true;
                        break;
                    }
                }

                if (!found)
                    throw new InvalidOperationException(string.Format(Resources.ExcelWorksheetNotFound + " [{0}]！", this.Document.WorksheetName));

                string sql = string.Format("select * from [{0}]", worksheetName);
                OleDbCommand comm = new OleDbCommand(sql, conn);
                using (OleDbDataReader reader = comm.ExecuteReader())
                {
                    if (reader.FieldCount == 0)
                        throw new InvalidOperationException(Resources.ExcelNotContainValidContent);

                    DataTable loadedTable = null;
                    while (reader.Read())
                    {
                        if (loadedTable == null)
                        {
                            loadedTable = new DataTable(this.Document.WorksheetName);
                            for (int i = 0; i < reader.FieldCount; i++)
                                loadedTable.Columns.Add(reader.GetName(i));
                            loadedTable.AcceptChanges();
                        }

                        bool hasValue = false;
                        DataRow dataRow = loadedTable.NewRow();
                        for (int i = 0; i < reader.FieldCount; i++)
                        {
                            object fieldValue = reader[i];
                            dataRow[reader.GetName(i)] = fieldValue;
                            if (!hasValue && fieldValue != null && fieldValue != DBNull.Value)
                                hasValue = true;
                        }

                        if (hasValue)
                            loadedTable.Rows.Add(dataRow);
                    }

                    DataTable dataTableToReturn = DataColumnNameTrim(loadedTable);
                    this.ValidateExcelDataTable(dataTableToReturn);

                    dataTableToReturn.AcceptChanges();
                    return dataTableToReturn;
                }
            }
        }

        /// <summary>
        /// Validate excel data table.
        /// </summary>
        /// <param name="datatable"></param>
        /// <param name="exception"></param>
        /// <returns></returns>
        private void ValidateExcelDataTable(DataTable datatable)
        {
            foreach (ExcelField excelField in this.Document.Fields)
            {
                if (!datatable.Columns.Contains(excelField.FieldName) && !excelField.IsNullable)
                {
                    throw new InvalidOperationException(string.Format(Resources.ExcelNotContainRequiredColumn, excelField.FieldName));
                }
            }
        }

        /// <summary>
        /// Convert cell value to target type by ExcelFieldTypes
        /// </summary>
        /// <param name="excelField"></param>
        /// <param name="cellValue"></param>
        /// <returns></returns>
        private object ConvertFieldValue(ExcelField excelField, object cellValue)
        {
            if (cellValue == null) return null;

            object fieldValue = null;
            switch (excelField.FieldType)
            {
                case ExcelFieldTypes.Boolean:
                    fieldValue = trueValues.Contains(cellValue.ToString().ToLowerInvariant());
                    break;
                case ExcelFieldTypes.DateTime:
                    fieldValue = DateTime.Parse(cellValue.ToString());
                    break;
                case ExcelFieldTypes.Decimal:
                    fieldValue = decimal.Parse(cellValue.ToString());
                    break;
                case ExcelFieldTypes.Double:
                    fieldValue = double.Parse(cellValue.ToString());
                    break;
                case ExcelFieldTypes.Int32:
                    fieldValue = int.Parse(cellValue.ToString());
                    break;
                case ExcelFieldTypes.Text:
                    fieldValue = cellValue.ToString();
                    break;
                default:
                    throw new NotSupportedException(string.Format(Resources.NotSupportExcelType, excelField.FieldType));
            }

            return fieldValue;
        }

        /// <summary>
        /// Map ExcelFieldTypes enumeration to System.Type.
        /// </summary>
        /// <param name="fieldType"></param>
        /// <returns></returns>
        private static Type MapExcelFieldType(ExcelFieldTypes fieldType)
        {
            switch (fieldType)
            {
                case ExcelFieldTypes.Boolean:
                    return typeof(bool);
                case ExcelFieldTypes.DateTime:
                    return typeof(DateTime);
                case ExcelFieldTypes.Decimal:
                    return typeof(decimal);
                case ExcelFieldTypes.Double:
                    return typeof(double);
                case ExcelFieldTypes.Int32:
                    return typeof(int);
                case ExcelFieldTypes.Text:
                    return typeof(string);
                default:
                    throw new NotSupportedException(string.Format(Resources.NotSupportExcelType, fieldType));
            }
        }

        /// <summary>
        /// Trim data column name space and break line.
        /// </summary>
        /// <param name="datatable"></param>
        /// <returns></returns>
        private static DataTable DataColumnNameTrim(DataTable datatable)
        {
            if (datatable == null || datatable.Columns.Count == 0)
            {
                return datatable;
            }

            for (int i = 0; i < datatable.Columns.Count; i++)
            {
                datatable.Columns[i].ColumnName = datatable.Columns[i].ColumnName.Trim();
            }

            return datatable;
        }
    }
}
